package Dao.impl;

import BaseQuery.BaseQuery;
import BaseQuery.UserQuery;
import Bean.User;
import Dao.IUserDao;
import Utils.JdbcUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDao implements IUserDao {

    QueryRunner queryRunner;

    public UserDao(){
        queryRunner = JdbcUtils.getQueryRunner();
    }

    @Override
    public int add(User user) throws SQLException {
        System.out.println("1111111111111");
       String sql = "insert into t_user(password, userName, userSex, userImg,delTag) values(?,?,?,?,?)";
        return queryRunner.update(sql,
                user.getPassword(),
                user.getUserName(),
                user.getUserSex(),
                user.getUserImg(),
                user.getDelTag());
    }

    @Override
    public int update(User user) throws SQLException {
        String sql = "update t_user set password=?, userName=?, userSex=?, userImg=?,delTag=? where userId=?";
        return queryRunner.update(sql, user.getPassword(),
                user.getUserName(),
                user.getUserSex(),
                user.getUserImg(),
                user.getDelTag(),
                user.getUserId());
    }

    public int delete(Integer id) throws SQLException {
        //这里不需要删除标记，否则无法正常进行删除
        String sql = "delete  from t_user where userId=?";
        return queryRunner.update(sql,id);
    }

    public User getById(Integer id) throws SQLException {
        String sql = "select userId, password, userName, userSex, userImg, delTag from t_user where userId=?";
        return queryRunner.query(sql,new BeanHandler<User>(User.class),id);
    }

    @Override
    public List<User> getAll() throws SQLException {
        String sql = "select userId, password, userName, userSex, userImg, delTag from t_user";
        return queryRunner.query(sql,new BeanListHandler<User>(User.class));
    }

    @Override
    public int getCount(BaseQuery baseQuery) throws SQLException {

        UserQuery userQuery = (UserQuery)baseQuery;

        List<Object> params = new ArrayList<>();
        String sql = "select count(userId) from t_user where 1=1";

        if(userQuery.getUserName() != null){
            sql += " and userName like concat('%',?,'%')";
            params.add(userQuery.getUserName());

        }
        System.out.println("查询总记录："+sql);
        System.out.println("参数集合："+params);

        return queryRunner.query(sql,new ScalarHandler<Long>(),params.toArray()).intValue();
    }
    @Override
    public List<User> getByPage(BaseQuery baseQuery) throws SQLException {

        UserQuery userQuery = (UserQuery)baseQuery;

        List<Object> params = new ArrayList<>();
        String sql = "select userId, password, userName, userSex, userImg, delTag from t_user"+
                " where 1=1";

        if(userQuery.getUserName() != null){
            sql += " and userName like concat('%',?,'%')";
            params.add(userQuery.getUserName());

        }
        sql += " order by userId desc limit ?,?";

        params.add(userQuery.getstartIndex());
        params.add(userQuery.getPageCount());

        System.out.println("查询集合："+sql);
        System.out.println("参数集合："+params);

        return queryRunner.query(sql,new BeanListHandler<User>(User.class),params.toArray());
    }
}
